const mysql = require('promise-mysql');
const fs = require('fs');
const xlsx = require('xlsx');
const _ = require('lodash');

let filePath = './卡号密码.xlsx';
let rs = fs.createReadStream(filePath);

let pwdChars = ["1", "2", "3", "4", "5", "6", "7", "8", "9",
    "0", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "m",
    "n", "p", "r", "s", "t", "u", "v", "w", "x", "y", "z", "A", "B",
    "C", "D", "E", "F", "G", "H", "I", "J", "K", "M", "N", "P", "R",
    "S", "T", "U", "V", "W", "X", "Y", "Z"];

function process_RS(stream/*:ReadStream*/, cb/*:(wb:Workbook)=>void*/)/*:void*/{
    var buffers = [];
    stream.on('data', function(data) { buffers.push(data); });
    stream.on('end', function() {
        var buffer = Buffer.concat(buffers);
        // buffer = iconv.decode(buffer,'gbk');
        var workbook = xlsx.read(buffer, {type:"buffer"});

        /* DO SOMETHING WITH workbook IN THE CALLBACK */
        cb(workbook);
    });
}

async function getConn(){
    let conn = await mysql.createConnection({
        host: '123.206.23.123',
        user: 'root',
        password: 'xie0316lei',
        database: 'ixiansheng20171109'
    });

    return conn;
}

let dataSource = function () {
    let pool = null;

    return async function(){
        if(!pool){
            pool = await mysql.createPool({
                connectionLimit : 10,
                host: '123.206.23.123',
                user: 'root',
                password: 'xie0316lei',
                database: 'ixiansheng20171109'
            });
        }
        return pool;
    }
}();

/**
 * 读excel文件，把遗漏的VIP卡补充回库里。
 * @returns {Promise<void>}
 */
async function main(){
    let conn = await getConn();

    let wb = process_RS(rs, async (wb) => {
        let first_sheet_name = wb.SheetNames[0];
        let ws = wb.Sheets[first_sheet_name];

        let result = {};
        let typeMap = {};

        for (let m in ws) {
            /* all keys that do not begin with "!" correspond to cell addresses */
            if (m[0] === '!') continue;
            let value = ws[m].w;
            let values = value.split(/;+/);

            let type = m.match(/([A-Z])(.*)/);
            if(!type) continue;
            let row = type[2];
            let col = type[1];


            if(row > 1){
                if(col === 'A'){
                    let vipCard = await conn.query(`select * from vip_card where vip_card_number = ${value}`);
                    if(vipCard.length === 0){
                        result.number = value;
                        result.insert = true;
                    }else{
                        result.insert = false;
                    }
                }else if(col === 'B'){
                    result.password = value;
                    if(result.insert){
                        let params = [result.number, result.password, new Date(), new Date(), 0];
                        console.log(params);
                        let r = await conn.query(`insert into vip_card (vip_card_number, vip_card_password, created_on, updated_on, steward_id) values (?,?,?,?,?)`, params);
                        console.log(r);
                    }
                }
            }
            // checkIds.push(ws[m].w);
        }

        conn.end();
    });
}

/**
 * 批量生成VIP测试卡记录
 * @param start
 * @param count
 * @returns {Promise<void>}
 */
async function addTestVip(start, count){
    let conn = await getConn();

    for(let i = 0; i < count; i++){
        let vipCardNumber = +start + i;
        let pwd = vipCardNumber.toString().slice(2, 6);

        let params = [vipCardNumber, pwd, new Date(), new Date(), 1643];
        let r = await conn.query(`insert into vip_card (vip_card_number, vip_card_password, created_on, updated_on, steward_id) values (?,?,?,?,?)`, params);
    }
}

async function validBoxSubscribe(){
    let conn = await getConn();
    let count = 0;

    let vipUser = await conn.query('select id,user_name from user where vip_status = 7');
    // let vipUser = await conn.query('select user_id from (select user_id , status, price, count(*) as \'sum\' from deposit where status in (1,5) group by 1,2) as t where sum >= 3');

    for(let user of vipUser){
        let subscribe = await conn.query(`select * from deposit_subscibe where user_id = ${user.id} and product_code = 'BOX00999' and deleted = 0 and pay_status = 1`);
        if(subscribe.length == 0){
            console.log(user);
            count++;
            let params = [user.id, '00000000000000000', new Date(), new Date(), 999, 0, 902,1, 'BOX00999', 999];
            let r = await conn.query(`insert into deposit_subscibe (user_id, order_no, created_on, updated_on, total_number, used_number, product_id,pay_status, product_code, residue_number) values (?, ?, ?, ?, ?, ?, ?,?, ?, ?)`, params);
        }
    }
    console.log(count);
}

async function getMaxVipCardNum(){
    let pool = await dataSource();

    let maxCard = await pool.query(`select vip_card_number from vip_card order by vip_card_number desc limit 1`);

    if(maxCard.length == 0){
          return 0;
    }
    return maxCard[0].vip_card_number;
}

async function createNewCard(amount){
    let account = +(await getMaxVipCardNum());

    if(amount <= 0){
        throw '生成数量必须大于0'
    }

    for(let i = 1; i <= amount; i++){
        let numStr = new String(account + 1);
        numStr = numStr.replace(/4/g, 5);

        account = +numStr;
        let pwd = genPwdCode(4);
        console.log(numStr, pwd);
        await saveNewVipCard({num : numStr, pwd : pwd})
    }

}

function genPwdCode(length){
    let result = '';
    for(let i = 0; i < length; i++){
        result += pwdChars[Math.floor((Math.random() * pwdChars.length))];
    }
    return result;
}

async function saveNewVipCard(entity){
    let pool = await dataSource();
    let params = [entity.num, entity.pwd, new Date(), new Date(), 99999];
    let r = await pool.query(`insert into vip_card (vip_card_number, vip_card_password, created_on, updated_on, steward_id) values (?,?,?,?,?)`, params);
    return r;
}

// main();
// addTestVip('400012',39);
// validBoxSubscribe();
// createNewCard(2);